1 Imports System.Data.SqlClient
2 Imports System.IO
3 Imports Excel = Microsoft.Office.Interop.Excel
4 Public Class frmStaffRecord
5 Public Sub GetData()
6 Try
7 con = New SqlConnection(cs)
8 con.Open()
9 cmd = New SqlCommand("Select RTRIM(St_ID) as [ID], RTRIM(StaffID) as [Staff ID], RTRIM(StaffName) as [Staff Name], Convert(DateTime,DateOfJoining,103) as [Joining Date], RTRIM(Gender) as [Gender], RTRIM(FatherName) as [Father's Name], RTRIM(TemporaryAddress) as [Temporary Address], RTRIM(PermanentAddress) as [Permanent Address], RTRIM(Designation) as [Designation], RTRIM(Qualifications) as [Qualifications], Convert(DateTime,DOB,103) as [DOB], RTRIM(PhoneNo) as [Phone No.], RTRIM(MobileNo) as [Mobile No.], RTRIM(Staff.Email) as [Email ID],RTRIM(SchoolID) as [School ID],RTRIM(SchoolName) as [School Name],RTRIM(ClassType) as [Class Type],RTRIM(Salary) as [Basic Salary],RTRIM(AccountName) as [Account Name],RTRIM(AccountNumber) as [Account No.],RTRIM(Bank) as [Bank],RTRIM(Branch) as [Branch],RTRIM(IFSCcode) as [IFSC Code], Photo,RTRIM(Status) as [Status] from Staff,ClassType,SchoolInfo where Staff.ClassType=ClassType.Type and Staff.SchoolID=SchoolInfo.S_ID order by StaffName", con)
10 adp = New SqlDataAdapter(cmd)
11 ds = New DataSet()
12 adp.Fill(ds, "Staff")
13 dgw.DataSource = ds.Tables("Staff").DefaultView
14 con.Close()
15 Catch ex As Exception
16 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
17 End Try
18 End Sub
19
20 Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
21 Me.Close()
22 End Sub
23
24
25 Sub Reset()
26 txtStaffName.Text = ""
27 dtpDateFrom.Text = Today
28 dtpDateTo.Text = Today
29 GetData()
30 End Sub
31 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
32 Reset()
33 End Sub
34
35 Private Sub frmStudentRecord_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
36 GetData()
37 End Sub
38
39 Private Sub dgw_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
40 Try
41 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
42 If lblSet.Text = "Staff Entry" Then
43 Me.Hide()
44 frmStaff.Show()
45 frmStaff.txtID.Text = dr.Cells(0).Value.ToString()
46 frmStaff.txtStaffID.Text = dr.Cells(1).Value.ToString()
47 frmStaff.txtStaffName.Text = dr.Cells(2).Value.ToString()
48 frmStaff.dtpDateOfJoining.Text = dr.Cells(3).Value.ToString()
49 If (dr.Cells(4).Value.ToString() = "Male") Then
50 frmStaff.rbMale.Checked = True
51 End If
52 If (dr.Cells(2).Value.ToString() = "Female") Then
53 frmStaff.rbFemale.Checked = True
54 End If
55 frmStaff.txtFatherName.Text = dr.Cells(5).Value.ToString()
56 frmStaff.txtTempAddress.Text = dr.Cells(6).Value.ToString()
57 frmStaff.txtPermanentAddress.Text = dr.Cells(7).Value.ToString()
58 frmStaff.cmbDesignation.Text = dr.Cells(8).Value.ToString()
59 frmStaff.txtQualifications.Text = dr.Cells(9).Value.ToString()
60 frmStaff.dtpDOB.Text = dr.Cells(10).Value.ToString()
61 frmStaff.txtPhoneNo.Text = dr.Cells(11).Value.ToString()
62 frmStaff.txtMobileNo.Text = dr.Cells(12).Value.ToString()
63 frmStaff.txtEmail.Text = dr.Cells(13).Value.ToString()
64 frmStaff.txtSchoolID.Text = dr.Cells(14).Value.ToString()
65 frmStaff.cmbSchoolName.Text = dr.Cells(15).Value.ToString()
66 frmStaff.cmbClassType.Text = dr.Cells(16).Value.ToString()
67 frmStaff.txtBasicSalary.Text = dr.Cells(17).Value.ToString()
68 frmStaff.txtAccountName.Text = dr.Cells(18).Value.ToString()
69 frmStaff.txtAccountNo.Text = dr.Cells(19).Value.ToString()
70 frmStaff.txtBank.Text = dr.Cells(20).Value.ToString()
71 frmStaff.txtBranch.Text = dr.Cells(21).Value.ToString()
72 frmStaff.txtIFSCcode.Text = dr.Cells(22).Value.ToString()
73 Dim data As Byte() = DirectCast(dr.Cells(23).Value, Byte())
74 Dim ms As New MemoryStream(data)
75 frmStaff.Picture.Image = Image.FromStream(ms)
76 frmStaff.cmbStatus.Text = dr.Cells(24).Value.ToString()
77 frmStaff.btnUpdate.Enabled = True
78 frmStaff.btnDelete.Enabled = True
79 frmStaff.btnSave.Enabled = False
80 con = New SqlConnection(cs)
81 con.Open()
82 cmd = New SqlCommand("SELECT Department.ID,DepartmentName from Staff,Department,Staff_Department where Staff.St_ID=Staff_Department.StaffID and Department.ID=Staff_Department.DepartmentID and Staff.St_ID=" & dr.Cells(0).Value & "", con)
83 rdr = cmd.ExecuteReader()
84 While rdr.Read()
85
86 Dim lst As New ListViewItem()
87 lst.SubItems.Add(rdr(0))
88 lst.SubItems.Add(rdr(1).ToString().Trim())
89 frmStaff.ListView1.Items.Add(lst)
90 End While
91 con = New SqlConnection(cs)
92 con.Open()
93 cmd = con.CreateCommand()
94 cmd.CommandText = "SELECT ClassType,Designation FROM Staff where St_iD=@d1"
95 cmd.Parameters.AddWithValue("@d1", dr.Cells(0).Value)
96 rdr = cmd.ExecuteReader()
97 If rdr.Read() Then
98 frmStaff.cmbClassType.Text = rdr.GetValue(0)
99 frmStaff.cmbDesignation.Text = rdr.GetValue(1)
100 End If
101 If (rdr IsNot Nothing) Then
102 rdr.Close()
103 End If
104 If con.State = ConnectionState.Open Then
105 con.Close()
106 End If
107 lblSet.Text = ""
108 End If
109 If lblSet.Text = "Bus Holder Entry" Then
110 Me.Hide()
111 frmBusCardHolder_Staff.Show()
112 frmBusCardHolder_Staff.txtS_ID.Text = dr.Cells(0).Value.ToString()
113 frmBusCardHolder_Staff.txtStaffID.Text = dr.Cells(1).Value.ToString()
114 frmBusCardHolder_Staff.txtStaffName.Text = dr.Cells(2).Value.ToString()
115 frmBusCardHolder_Staff.txtSchoolName.Text = dr.Cells(15).Value.ToString()
116 lblSet.Text = ""
117 End If
118 Catch ex As Exception
119 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
120 End Try
121 End Sub
122
123 Private Sub dgw_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
124 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
125 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
126 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
127 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
128 End If
129 Dim b As Brush = SystemBrushes.ControlText
130 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
131
132 End Sub
133
134 Private Sub txtStaffName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtStaffName.TextChanged
135 Try
136 con = New SqlConnection(cs)
137 con.Open()
138 cmd = New SqlCommand("Select RTRIM(St_ID) as [ID], RTRIM(StaffID) as [Staff ID], RTRIM(StaffName) as [Staff Name], Convert(DateTime,DateOfJoining,103) as [Joining Date], RTRIM(Gender) as [Gender], RTRIM(FatherName) as [Father's Name], RTRIM(TemporaryAddress) as [Temporary Address], RTRIM(PermanentAddress) as [Permanent Address], RTRIM(Designation) as [Designation], RTRIM(Qualifications) as [Qualifications], Convert(DateTime,DOB,103) as [DOB], RTRIM(PhoneNo) as [Phone No.], RTRIM(MobileNo) as [Mobile No.], RTRIM(Staff.Email) as [Email ID],RTRIM(SchoolID) as [School ID],RTRIM(SchoolName) as [School Name],RTRIM(ClassType) as [Class Type],RTRIM(Salary) as [Basic Salary],RTRIM(AccountName) as [Account Name],RTRIM(AccountNumber) as [Account No.],RTRIM(Bank) as [Bank],RTRIM(Branch) as [Branch],RTRIM(IFSCcode) as [IFSC Code], Photo,RTRIM(Status) as [Status] from Staff,ClassType,SchoolInfo where Staff.ClassType=ClassType.Type and Staff.SchoolID=SchoolInfo.S_ID where Staffname like '" & txtStaffName.Text & "%' order by StaffName", con)
139 adp = New SqlDataAdapter(cmd)
140 ds = New DataSet()
141 adp.Fill(ds, "Staff")
142 dgw.DataSource = ds.Tables("Staff").DefaultView
143 con.Close()
144 Catch ex As Exception
145 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
146 End Try
147 End Sub
148
149 Private Sub dtpDateTo_Validating(sender As System.Object, e As System.ComponentModel.CancelEventArgs) Handles dtpDateTo.Validating
150 If (dtpDateFrom.Value.Date) > (dtpDateTo.Value.Date) Then
151 MessageBox.Show("Invalid Selection", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
152 dtpDateTo.Focus()
153 End If
154 End Sub
155
156 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
157 Try
158 con = New SqlConnection(cs)
159 con.Open()
160 cmd = New SqlCommand("Select RTRIM(St_ID) as [ID], RTRIM(StaffID) as [Staff ID], RTRIM(StaffName) as [Staff Name], Convert(DateTime,DateOfJoining,103) as [Joining Date], RTRIM(Gender) as [Gender], RTRIM(FatherName) as [Father's Name], RTRIM(TemporaryAddress) as [Temporary Address], RTRIM(PermanentAddress) as [Permanent Address], RTRIM(Designation) as [Designation], RTRIM(Qualifications) as [Qualifications], Convert(DateTime,DOB,103) as [DOB], RTRIM(PhoneNo) as [Phone No.], RTRIM(MobileNo) as [Mobile No.], RTRIM(Staff.Email) as [Email ID],RTRIM(SchoolID) as [School ID],RTRIM(SchoolName) as [School Name],RTRIM(ClassType) as [Class Type],RTRIM(Salary) as [Basic Salary],RTRIM(AccountName) as [Account Name],RTRIM(AccountNumber) as [Account No.],RTRIM(Bank) as [Bank],RTRIM(Branch) as [Branch],RTRIM(IFSCcode) as [IFSC Code], Photo,RTRIM(Status) as [Status] from Staff,ClassType,SchoolInfo where Staff.ClassType=ClassType.Type and Staff.SchoolID=SchoolInfo.S_ID where DateOfJoining between @d1 and @d2 order by StaffName", con)
161 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
162 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
163 adp = New SqlDataAdapter(cmd)
164 ds = New DataSet()
165 adp.Fill(ds, "Staff")
166 dgw.DataSource = ds.Tables("Staff").DefaultView
167 con.Close()
168 Catch ex As Exception
169 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
170 End Try
171 End Sub
172 End Class
